<?php

namespace common;
/**
 *批量更新
 *
 *author :jasonYe 651629095@qq.com
 *createTime:2020/2/18 10:33
 */
class batchUpdate
{
    /**
     * 批量更新函数
     * @param $data array 待更新的数据，二维数组格式
     * @param array $params array 值相同的条件，键值对应的一维数组
     * @param string $field string 值不同的条件，默认为id
     * @return bool|string
     */
    public static function batchUpdate($data, $field, $table, $params = [])
    {
        if (!is_array($data) || !$field || !is_array($params)) {
            return false;
        }

        $updates = self::parseUpdate($data, $field);
        $where = self::parseParams($params);
        // 获取所有键名为$field列的值，值两边加上单引号，保存在$fields数组中
        $fields = array_column($data, $field);
        $fields = implode(',', array_map(function ($value) {
            return "'" . $value . "'";
        }, $fields));
        $sql = sprintf("UPDATE `%s` SET %s WHERE `%s` IN (%s) %s", $table, $updates, $field, $fields, $where);
        return $sql;
    }

    /**
     * 将二维数组转换成CASE WHEN THEN的批量更新条件
     * @param $data array 二维数组
     * @param $field string 列名
     * @return string sql语句
     */
    public static function parseUpdate($data, $field)
    {
        $sql = '';
        $keys = array_keys(current($data));
        foreach ($keys as $column) {
            $sql .= sprintf("`%s` = CASE `%s` \n", $column, $field);
            foreach ($data as $line) {
                $sql .= sprintf("WHEN '%s' THEN '%s' \n", $line[$field], $line[$column]);
            }
            $sql .= "END,";
        }
        return rtrim($sql, ',');
    }

    /**
     * 解析where条件
     * @param $params
     * @return array|string
     */
    public static function parseParams($params)
    {
        $where = [];
        foreach ($params as $key => $value) {
            $where[] = sprintf("`%s` = '%s'", $key, $value);
        }
        return $where ? ' AND ' . implode(' AND ', $where) : '';
    }

    public static function batchUpdateSql($table, $data,$ignoreFileds=[])
    {
        $insertSql = " insert into  `$table`   ";
        $insertDataSql = "";
        foreach ($data as $key => $itemData) {
            if (0 == $key) {
                $insertSql .= "(";
                $lastInsertSql = " ON DUPLICATE KEY UPDATE  ";
                $num = 1;
                $total = count($itemData);
                foreach ($itemData as $newKey => $newItem) {
                    $insertSql .= "`" . $newKey . "`";
                    if(!in_array($newKey,$ignoreFileds)){
                        $lastInsertSql .= $newKey . "=" . " VALUES(" . $newKey . ")";
                    }
                    if ( $total > $num) {
                        $insertSql .= ",";
                        if(!in_array($newKey,$ignoreFileds)){
                            $lastInsertSql .= ",";
                        }
                    }
                    $num++;
                }
                $insertSql .= ")  values ";

            }
            $num = 0;
            foreach ($itemData as $newKey => $newItem) {
                $num == 0 && $insertDataSql .= "(";
                $insertDataSql .= "'" . addslashes($newItem) . "'";
                if (count($itemData) - 1 == $num) {
                    $insertDataSql .= ")";
                    count($data) - 1 != $key && $insertDataSql .= ",";
                } else {
                    $insertDataSql .= ",";
                }
                $num++;
            }
        }
        return $insertSql . $insertDataSql . substr($lastInsertSql,0,strlen($lastInsertSql)-1);
    }
}